package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.zql.entity.AgencyEntity;

import util.DBTool;

public class AgencyDao {
	public void save(AgencyEntity agency) {
		Connection conn = null;
		try {
			conn = DBTool.getConnection();
			//获取当前日期
			SimpleDateFormat sdf = new SimpleDateFormat("_yyyy_MM_dd");
			String date = sdf.format(new Date());
			String sql = "INSERT INTO \"201906.tDealer\" "
					+ "(\"sDealerName\",\"nBrandID\",\"sProvince\",\"sCity\",\"sAddress\",\"sSaleCall\",\"sCustomerServiceCall\",\"sDealerType\",\"nManufacturerID\",\"nState\",\"dOpeningDate\",\"dCloseDate\",\"dUpdateTime\",\"nDealerIDWeb\",\"sBrand\",\"sManufacturer\") "
					+ "VALUES "
					+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			//ps.setInt(1, agency.getnDealerID());
			ps.setString(1, agency.getsDealerName());
			ps.setInt(2, agency.getnBrandID());
			ps.setString(3, agency.getsProvince());
			ps.setString(4, agency.getsCity());
			ps.setString(5, agency.getsAddress());
			ps.setString(6, agency.getsSaleCall());
			ps.setString(7, agency.getsCustomerServiceCall());
			ps.setString(8, agency.getsDealerType());
			ps.setInt(9, agency.getnManufacturerID());
			ps.setInt(10, agency.getnState());
			ps.setDate(11, agency.getdOpeningDate());
			ps.setDate(12, agency.getdCloseDate());
			ps.setTimestamp(13, agency.getdUpdateTime());
			ps.setInt(14, agency.getnDealerIDWeb());
			ps.setString(15, agency.getsBrand());
			ps.setString(16, agency.getsManufacturer());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBTool.close(conn);
			
		}
	}
	
	public List<AgencyEntity> getAll(String tableName) {
		//读取数据库里的数据,并存放到一个List中。
				Connection conn = null;
				List<AgencyEntity> list = new ArrayList<AgencyEntity>();
				try {
					conn = DBTool.getConnection();
					String sql = "SELECT * FROM dealers."+tableName;
					Statement smt = conn.createStatement();
					ResultSet rs = smt.executeQuery(sql);
					while(rs.next()) {
						
						AgencyEntity agency = new AgencyEntity();
						//agency.setnDealerID(rs.getInt("nDealerID"));
						agency.setsDealerName(rs.getString("sDealerName"));
						agency.setnBrandID(rs.getInt("nBrandID"));
						agency.setsProvince(rs.getString("sProvince"));
						agency.setsCity(rs.getString("sCity"));
						agency.setsAddress(rs.getString("sAddress"));
						agency.setsSaleCall(rs.getString("sSaleCall"));
						agency.setsCustomerServiceCall(rs.getString("sCustomerServiceCall"));
						agency.setsDealerType(rs.getString("sDealerType"));
						agency.setnManufacturerID(rs.getInt("nManufacturerID"));
						agency.setnState(rs.getInt("nState"));
						agency.setdOpeningDate(rs.getDate("dOpeningDate"));
						agency.setdCloseDate(rs.getDate("dCloseDate"));
						agency.setdUpdateTime(rs.getTimestamp("dUpdateTime"));
						agency.setnDealerIDWeb(rs.getInt("nDealerIDWeb"));
						agency.setsBrand(rs.getString("sBrand"));
						agency.setsManufacturer(rs.getString("sManufacturer"));
						
						
						
						
						list.add(agency);
						
						
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					DBTool.close(conn);
				}
		return list;
	}
	
	
}
